# -*- coding: gbk -*-
import pandas as pd
import datetime
import re

pd.set_option('display.max_rows',None) #显示所有行
pd.set_option('display.max_columns',None) #显示所有列
pd.set_option('display.width',1000) #设置列宽度1000

#数据文件路径
#file = 'C:/work/cl/115810E151011181201121000300.txt'  #粤科
#file = 'C:/work/cl/115960U019030190322121003800.txt' #兴宁珠江
#file = 'C:/work/cl/118880U010005190202121000100.txt'  #客商银行
#file = 'C:/work/cl/118880U010005190621121000100.txt'  #客商银行2
#file = 'C:/work/cl/115810K992055190613121000300.txt'
#file = 'C:/work/cl/115810K992055190613121000200.txt'
#file = 'C:/work/cl/115810655A002190830121000100.txt' #粤电20190830
#file = 'C:/work/cl/115810655A002180919121000100.txt' #粤电20180919

file = 'd:/check/yuecai/115810655A002191016121000100.txt'


#信息类型装载区
infoType_08 = pd.DataFrame(columns=['贷款卡编码','贷款合同号码','业务发生日期','合同有效状态'])
infoType_09 = pd.DataFrame(columns=['贷款卡编码','贷款合同号码','业务发生日期','借据编号','借据余额'])
infoType_13 = pd.DataFrame(columns=['票据内部编号','业务发生日期','贷款卡编码','贴现金额','票据状态'])
infoType_19 = pd.DataFrame(columns=['保函号码','业务发生日期','贷款卡编码','保函状态','保函开立日期','保函到期日','保函余额','余额发生日期'])
infoType_20 = pd.DataFrame(columns=['汇票号码','业务发生日期','贷款卡编码','汇票金额','汇票状态'])
infoType_22 = pd.DataFrame(columns=['贷款卡编码','主合同编号','业务发生日期','保证合同编码','担保人贷款卡编码','保证金额','保证担保形式','合同有效状态'])
infoType_23 = pd.DataFrame(columns=['贷款卡编码','主合同编号','业务发生日期','抵押合同编码','抵押序号','担保人贷款卡编码','抵押金额','合同有效状态'])
infoType_24 = pd.DataFrame(columns=['贷款卡编码','主合同编号','业务发生日期','质押合同编码','质押序号','担保人贷款卡编码','质押金额','合同有效状态'])
infoType_32 = pd.DataFrame(columns=['贷款卡编码','主合同编号','业务发生日期','保证合同编码','证件类型','证件号码','保证金额','保证担保形式','合同有效状态'])
infoType_33 = pd.DataFrame(columns=['贷款卡编码','主合同编号','业务发生日期','抵押合同编码','抵押序号','证件类型','证件号码','抵押金额','合同有效状态'])
infoType_34 = pd.DataFrame(columns=['贷款卡编码','主合同编号','业务发生日期','质押合同编码','质押序号','证件类型','证件号码','质押金额','合同有效状态'])
infoType_26 = pd.DataFrame(columns=['贷款卡编码','欠息余额','欠息类型','欠息余额改变日期'])

#信息类型匹配区
pattern_08 = '.{18}(.{16})(.{60}).{1}(.{8}).{20}[C.{61}]*D.{158}([12])[E.{43}]+'
pattern_09 = '.{18}(.{16})(.{60}).{1}(.{8}).{20}[C.+]*F(.{60}).{23}(.{20}).{30}'
pattern_13 = '.{18}(.{60}).{60}.{1}(.{8}).{20}[C.{61}]*D.{81}(.{16}).{109}(.{20}).{36}(.{1}).{3}'
pattern_19 = '.{18}(.{60}).{61}(.{8}).{20}[C.{61}]*D.{80}(.{16}).{1}(.{1}).{23}(.{8})(.{8}).{5}(.{20})(.{8}).{1}'
pattern_20 = '.{18}.{60}(.{20}).{61}(.{8}).{20}[C.{61}]*D.{80}(.{16}).{3}(.{20}).{29}(.{1}).{1}'
pattern_22 = '.{18}(.{16})(.{60}).{2}(.{8}).{20}[C.+]*D(.{60}).{80}(.{16}).{3}(.{20}).{8}([123])([12])'
pattern_23 = '.{18}(.{16})(.{60}).{2}(.{8}).{20}[C.+]*E(.{60})(.{2}).{80}(.{16}).{133}(.{20}).{488}([12])'
pattern_24 = '.{18}(.{16})(.{60}).{2}(.{8}).{20}[C.+]*F(.{60})(.{2}).{80}(.{16}).{35}(.{20})([12])'
pattern_32 = '.{18}(.{16})(.{60}).{2}(.{8}).{20}[C.+]*G(.{60}).{80}([0-9ABCX])(.{18}).{3}(.{20}).{8}([123])([12])'
pattern_33 = '.{18}(.{16})(.{60}).{2}(.{8}).{20}[C.+]*H(.{60})(.{2}).{80}([0-9ABCX])(.{18}).{133}(.{20}).{488}([12])'
pattern_34 = '.{18}(.{16})(.{60}).{2}(.{8}).{20}[C.+]*I(.{60})(.{2}).{80}([0-9ABCX])(.{18}).{35}(.{20})([12])'
pattern_26 = '.{18}(.{16}).{29}[C.+]*D.{3}(.{20})([12])(.{8})'

yewuarray = []  #保存该机构有的业务类型



#读取数据，分配到信息类型装载区
for line in open(file):
    if line[0:4] == 'A2.1':
        yewucode = line[29:31]
        if yewucode == '11':
            yewuarray.append('贷款')
        elif yewucode == '12':
            yewuarray.append('保理')
        elif yewucode == '13':
            yewuarray.append('贴现')
        elif yewucode == '14':
            yewuarray.append('贸易融资')
        elif yewucode == '15':
            yewuarray.append('信用证')
        elif yewucode == '16':
            yewuarray.append('保函')
        elif yewucode == '17':
            yewuarray.append('承兑汇票')
        elif yewucode == '18':
            yewuarray.append('公开授信')
        elif yewucode == '19':
            yewuarray.append('担保业务')
        elif yewucode == '20':
            yewuarray.append('垫款')
        elif yewucode == '21':
            yewuarray.append('欠息')
        elif yewucode == '61':
            yewuarray.append('不良信贷资产')
    elif line[0:1] =='Z':
        if line[1:11]=='0000000000':
            yewuarray.pop()
    elif line[4:6] == '08': #贷款合同
        result = re.search(pattern_08.encode('gbk'), line.encode('gbk'))
        infoType_08 = infoType_08.append(pd.Series({'贷款卡编码':result.group(1).strip().decode('gbk'),'贷款合同号码':result.group(2).strip().decode('gbk'),'业务发生日期':datetime.datetime.strptime(result.group(3).decode('gbk'), '%Y%m%d').date(),'合同有效状态':result.group(4).decode('gbk')}),ignore_index=True)
    elif line[4:6] == '09': #贷款借据
        result = re.search(pattern_09.encode('gbk'), line.encode('gbk'))
        infoType_09 = infoType_09.append(pd.Series({'贷款卡编码': result.group(1).strip().decode('gbk'), '贷款合同号码': result.group(2).strip().decode('gbk'),'业务发生日期':datetime.datetime.strptime(result.group(3).decode('gbk'), '%Y%m%d').date(),'借据编号': result.group(4).strip().decode('gbk'),'借据余额': float(result.group(5).strip().decode('gbk'))}), ignore_index=True)
    elif line[4:6] == '10': #贷款还款
        pass
    elif line[4:6] == '11': #贷款展期
        pass
    elif line[4:6] == '12': #保理
        pass
    elif line[4:6] == '13': #票据贴现
        result = re.search(pattern_13.encode('gbk'), line.encode('gbk'))
        infoType_13 = infoType_13.append(pd.Series(
            {'票据内部编号': result.group(1).strip().decode('gbk'), '业务发生日期': datetime.datetime.strptime(result.group(2).decode('gbk'),'%Y%m%d').date(),'贷款卡编码': result.group(3).strip().decode('gbk'),'贴现金额': float(result.group(4).strip().decode('gbk')),'票据状态': result.group(5).strip().decode('gbk')}),ignore_index=True)
    elif line[4:6] == '14': #融资协议
        pass
    elif line[4:6] == '15': #融资业务
        pass
    elif line[4:6] == '16': #融资业务还款
        pass
    elif line[4:6] == '17': #融资业务展期
        pass
    elif line[4:6] == '18': #信用证
        pass
    elif line[4:6] == '19': #保函
        result = re.search(pattern_19.encode('gbk'), line.encode('gbk'))
        infoType_19 = infoType_19.append(pd.Series(
            {'保函号码': result.group(1).strip().decode('gbk'),'业务发生日期': datetime.datetime.strptime(result.group(2).decode('gbk'), '%Y%m%d').date(),
             '贷款卡编码': result.group(3).strip().decode('gbk'),'保函状态': result.group(4).decode('gbk'),'保函开立日期': datetime.datetime.strptime(result.group(5).decode('gbk'), '%Y%m%d').date(),'保函到期日': datetime.datetime.strptime(result.group(6).decode('gbk'), '%Y%m%d').date(),'保函余额':float(result.group(7).strip().decode('gbk')),'余额发生日期': datetime.datetime.strptime(result.group(8).decode('gbk'), '%Y%m%d').date()}), ignore_index=True)
    elif line[4:6] == '20': #银承汇票
        result = re.search(pattern_20.encode('gbk'), line.encode('gbk'))
        infoType_20 = infoType_20.append(pd.Series(
            {'汇票号码': result.group(1).strip().decode('gbk'),
             '业务发生日期': datetime.datetime.strptime(result.group(2).decode('gbk'), '%Y%m%d').date(),
             '贷款卡编码': result.group(3).strip().decode('gbk'), '汇票金额': float(result.group(4).strip().decode('gbk')),
             '汇票状态': result.group(5).strip().decode('gbk')}), ignore_index=True)
    elif line[4:6] == '21': #授信业务
        pass
    elif line[4:6] == '22': #保证合同
        result = re.search(pattern_22.encode('gbk'), line.encode('gbk'))
        #print('保证合同编号', result.group(4).strip().decode('gbk'),'贷款卡编码',result.group(1).strip().decode('gbk'))
        infoType_22 = infoType_22.append(pd.Series({'贷款卡编码':result.group(1).strip().decode('gbk'),'主合同编号':result.group(2).strip().decode('gbk'),'业务发生日期':datetime.datetime.strptime(result.group(3).decode('gbk'), '%Y%m%d').date(),'保证合同编码':result.group(4).strip().decode('gbk'),'担保人贷款卡编码':result.group(5).strip().decode('gbk'),'保证金额':float(result.group(6).strip().decode('gbk')),'保证担保形式':result.group(7).strip().decode('gbk'),'合同有效状态':result.group(8).strip().decode('gbk')}),ignore_index=True)
        #print(infoType_22)
    elif line[4:6] == '23': #抵押合同
        result = re.search(pattern_23.encode('gbk'), line.encode('gbk'))
        #print('抵押合同编号', result.group(4).strip().decode('gbk'),'贷款卡编码',result.group(1).strip().decode('gbk'))
        infoType_23 = infoType_23.append(pd.Series(
            {'贷款卡编码': result.group(1).strip().decode('gbk'), '主合同编号': result.group(2).strip().decode('gbk'),
             '业务发生日期': datetime.datetime.strptime(result.group(3).decode('gbk'), '%Y%m%d').date(),
             '抵押合同编码': result.group(4).strip().decode('gbk'),'抵押序号':result.group(5).strip().decode('gbk'), '担保人贷款卡编码': result.group(6).strip().decode('gbk'),
             '抵押金额': float(result.group(7).strip().decode('gbk')), '合同有效状态': result.group(8).strip().decode('gbk')}), ignore_index=True)
    elif line[4:6] == '24': #质押合同
        result = re.search(pattern_24.encode('gbk'), line.encode('gbk'))
        #print('质押合同编号', result.group(4).strip().decode('gbk'),'贷款卡编码',result.group(1).strip().decode('gbk'))
        infoType_24 = infoType_24.append(pd.Series(
            {'贷款卡编码': result.group(1).strip().decode('gbk'), '主合同编号': result.group(2).strip().decode('gbk'),
             '业务发生日期': datetime.datetime.strptime(result.group(3).decode('gbk'), '%Y%m%d').date(),
             '质押合同编码': result.group(4).strip().decode('gbk'), '质押序号': result.group(5).strip().decode('gbk'),
             '担保人贷款卡编码': result.group(6).strip().decode('gbk'),
             '质押金额': float(result.group(7).strip().decode('gbk')), '合同有效状态': result.group(8).strip().decode('gbk')}),
            ignore_index=True)
    elif line[4:6] == '32': #自然人保证合同
        result = re.search(pattern_32.encode('gbk'), line.encode('gbk'))
        #print('自然人保证合同编号', result.group(4).strip().decode('gbk'),'贷款卡编码',result.group(1).strip().decode('gbk'))
        infoType_32 = infoType_32.append(pd.Series(
            {'贷款卡编码': result.group(1).strip().decode('gbk'), '主合同编号': result.group(2).strip().decode('gbk'),
             '业务发生日期': datetime.datetime.strptime(result.group(3).decode('gbk'), '%Y%m%d').date(),
             '保证合同编码': result.group(4).strip().decode('gbk'), '证件类型': result.group(5).strip().decode('gbk'), '证件号码': result.group(6).strip().decode('gbk'),
             '保证金额': float(result.group(7).strip().decode('gbk')), '保证担保形式': result.group(8).strip().decode('gbk'),
             '合同有效状态': result.group(9).strip().decode('gbk')}), ignore_index=True)
    elif line[4:6] == '33': #自然人抵押合同
        result = re.search(pattern_33.encode('gbk'), line.encode('gbk'))
        #print(result)
        if(result != None) :
            #print('证件号码', result.group(7).strip().decode('gbk'),'贷款卡编码',result.group(1).strip().decode('gbk'))
            infoType_33 = infoType_33.append(pd.Series(
            {'贷款卡编码': result.group(1).strip().decode('gbk'), '主合同编号': result.group(2).strip().decode('gbk'),
             '业务发生日期': datetime.datetime.strptime(result.group(3).decode('gbk'), '%Y%m%d').date(),
             '抵押合同编码': result.group(4).strip().decode('gbk'), '抵押序号': result.group(5).strip().decode('gbk'),
             '证件类型': result.group(6).strip().decode('gbk'),'证件号码': result.group(7).strip().decode('gbk'),
             '抵押金额': float(result.group(8).strip().decode('gbk')), '合同有效状态': result.group(9).strip().decode('gbk')}),
            ignore_index=True)
    elif line[4:6] == '34': #自然人质押合同
        result = re.search(pattern_34.encode('gbk'), line.encode('gbk'))
        #print('自然人质押合同编号', result.group(4).strip().decode('gbk'),'证件号码',result.group(7).strip().decode('gbk'))
        infoType_34 = infoType_34.append(pd.Series(
            {'贷款卡编码': result.group(1).strip().decode('gbk'), '主合同编号': result.group(2).strip().decode('gbk'),
             '业务发生日期': datetime.datetime.strptime(result.group(3).decode('gbk'), '%Y%m%d').date(),
             '质押合同编码': result.group(4).strip().decode('gbk'), '质押序号': result.group(5).strip().decode('gbk'),
             '证件类型': result.group(6).strip().decode('gbk'),'证件号码': result.group(7).strip().decode('gbk'),
             '质押金额': float(result.group(8).strip().decode('gbk')), '合同有效状态': result.group(9).strip().decode('gbk')}),
            ignore_index=True)
    elif line[4:6] == '25': #垫款
        pass
    elif line[4:6] == '26': #欠息
        result = re.search(pattern_26.encode('gbk'), line.encode('gbk'))
        infoType_26 = infoType_26.append(pd.Series({'贷款卡编码': result.group(1).strip().decode('gbk'), '欠息余额': float(result.group(2).strip().decode('gbk')),'欠息类型': result.group(3).strip().decode('gbk'),'欠息余额改变日期': datetime.datetime.strptime(result.group(4).decode('gbk'),'%Y%m%d').date()}),ignore_index=True)


#打印业务类型
print('本机构有的业务类型',yewuarray)


#贷款合同处理区
infoType_08_do = infoType_08.sort_values(by='业务发生日期',inplace=False,ascending=True)
infoType_08_do.drop_duplicates(subset=['贷款卡编码','贷款合同号码'], keep='last', inplace=True)
infoType_08_do = infoType_08_do[infoType_08_do['合同有效状态']== '1']
infoType_08_do.drop_duplicates(subset=['贷款卡编码'], keep='last', inplace=True)
print('未结清贷款客户数：',infoType_08_do['贷款卡编码'].count())

#借据处理区
infoType_09_do = infoType_09.sort_values(by='业务发生日期',inplace=False,ascending=True)
infoType_09_do.drop_duplicates(subset=['借据编号'], keep='last', inplace=True)
infoType_09_do = infoType_09_do[infoType_09_do['借据余额'] != 0]
print('未结清借据客户数：',len(infoType_09_do['贷款卡编码'].unique()))
#print(infoType_09_do)
print('未结清贷款业务数：',infoType_09_do['借据编号'].count())
print('未结清贷款余额：',infoType_09_do['借据余额'].sum())
#保函处理区
infoType_19_do = infoType_19.sort_values(by=['保函号码','业务发生日期'],inplace=False,ascending=True)
infoType_19_do.drop_duplicates(subset=['保函号码','贷款卡编码'], keep='last', inplace=True)
infoType_19_do = infoType_19_do[infoType_19_do['保函状态'] == '1']
print('保函业务数:',len(infoType_19_do['保函号码'].unique()))
print('保函余额',infoType_19_do['保函余额'].sum())

#票据贴现处理区
infoType_13_do = infoType_13.sort_values(by=['票据内部编号','业务发生日期'],inplace=False,ascending=True)
infoType_13_do.drop_duplicates(subset=['票据内部编号','贷款卡编码'], keep='last', inplace=True)
infoType_13_do = infoType_13_do[infoType_13_do['票据状态'] == '1']
#print(infoType_13_do)
print('贴现业务数:',len(infoType_13_do['票据内部编号'].unique()))
print('贴现金额',infoType_13_do['贴现金额'].sum())

#承兑汇票处理区
infoType_20_do = infoType_20.sort_values(by=['汇票号码','业务发生日期'],inplace=False,ascending=True)
infoType_20_do.drop_duplicates(subset=['汇票号码','贷款卡编码'], keep='last', inplace=True)
infoType_20_do = infoType_20_do[infoType_20_do['汇票状态'] == '1']
#print(infoType_20_do)
print('汇票业务数:',len(infoType_20_do['汇票号码'].unique()))
print('汇票金额',infoType_20_do['汇票金额'].sum())

#保证处理区
infoType_22_do = infoType_22.sort_values(by=['保证合同编码','业务发生日期'],inplace=False,ascending=True)

infoType_22_do.drop_duplicates(subset=['主合同编号','保证合同编码','担保人贷款卡编码'], keep='last', inplace=True)
infoType_22_do = infoType_22_do[infoType_22_do['合同有效状态'] == '1']
#print(infoType_22_do)
print('保证业务数:',len(infoType_22_do['保证合同编码'].unique()))
infoType_22_do.drop_duplicates(subset=['保证合同编码','担保人贷款卡编码'], keep='last', inplace=True)
infoType_22_do_danbaoxingshi2 = infoType_22_do[infoType_22_do['保证担保形式'] == '2'].drop_duplicates(subset=['保证合同编码'], keep='last', inplace=False)
print('保证金额：',infoType_22_do[infoType_22_do['保证担保形式'] == '1']['保证金额'].sum() + infoType_22_do_danbaoxingshi2['保证金额'].sum() + infoType_22_do[infoType_22_do['保证担保形式'] == '3']['保证金额'].sum())

#抵押处理区
infoType_23_do = infoType_23.sort_values(by=['抵押合同编码','抵押序号','业务发生日期'],inplace=False,ascending=True)
infoType_23_do.drop_duplicates(subset=['主合同编号','抵押合同编码','抵押序号','担保人贷款卡编码'], keep='last', inplace=True)
infoType_23_do = infoType_23_do[infoType_23_do['合同有效状态'] == '1']
#print(infoType_23_do)
print('抵押业务数:',len(infoType_23_do['抵押合同编码'].unique()))
print('抵押金额：',infoType_23_do['抵押金额'].sum())

#质押处理区
infoType_24_do = infoType_24.sort_values(by=['质押合同编码','质押序号','业务发生日期'],inplace=False,ascending=True)
infoType_24_do.drop_duplicates(subset=['主合同编号','质押合同编码','质押序号','担保人贷款卡编码'], keep='last', inplace=True)
infoType_24_do = infoType_24_do[infoType_24_do['合同有效状态'] == '1']
print('质押业务数:',len(infoType_24_do['质押合同编码'].unique()))
print('质押金额：',infoType_24_do['质押金额'].sum())

#自然人保证处理区
infoType_32_do = infoType_32.sort_values(by=['保证合同编码','业务发生日期'],inplace=False,ascending=True)
infoType_32_do.drop_duplicates(subset=['主合同编号','保证合同编码','证件类型','证件号码'], keep='last', inplace=True)
infoType_32_do = infoType_32_do[infoType_32_do['合同有效状态'] == '1']
print('自然人保证业务数:',len(infoType_32_do['保证合同编码'].unique()))
infoType_32_do.drop_duplicates(subset=['保证合同编码','证件类型','证件号码'], keep='last', inplace=True)
infoType_32_do_danbaoxingshi2 = infoType_32_do[infoType_32_do['保证担保形式'] == '2'].drop_duplicates(subset=['保证合同编码'], keep='last', inplace=False)
print('自然人保证金额：',infoType_32_do[infoType_32_do['保证担保形式'] == '1']['保证金额'].sum() + infoType_32_do_danbaoxingshi2['保证金额'].sum() + infoType_32_do[infoType_32_do['保证担保形式'] == '3']['保证金额'].sum())

#自然人抵押处理区
infoType_33_do = infoType_33.sort_values(by=['抵押合同编码','抵押序号','业务发生日期'],inplace=False,ascending=True)
infoType_33_do.drop_duplicates(subset=['主合同编号','抵押合同编码','抵押序号','证件类型','证件号码'], keep='last', inplace=True)
infoType_33_do = infoType_33_do[infoType_33_do['合同有效状态'] == '1']
#print(infoType_33_do)
print('自然人抵押业务数:',len(infoType_33_do['抵押合同编码'].unique()))
print('自然人抵押金额：',infoType_33_do['抵押金额'].sum())

#自然人质押处理区
infoType_34_do = infoType_34.sort_values(by=['质押合同编码','质押序号','业务发生日期'],inplace=False,ascending=True)
infoType_34_do.drop_duplicates(subset=['主合同编号','质押合同编码','质押序号','证件类型','证件号码'], keep='last', inplace=True)
infoType_34_do = infoType_34_do[infoType_34_do['合同有效状态'] == '1']
print('自然人质押业务数:',len(infoType_34_do['质押合同编码'].unique()))
print('自然人质押金额：',infoType_34_do['质押金额'].sum())

#欠息处理区
infoType_26_do = infoType_26.sort_values(by='欠息余额改变日期',inplace=False,ascending=True)
infoType_26_do.drop_duplicates(subset=['贷款卡编码','欠息类型'], keep='last', inplace=True)
infoType_26_do = infoType_26_do[infoType_26_do['欠息余额'] != 0]
infoType_26_do_biaonei = infoType_26_do[infoType_26_do['欠息类型'] == '1']
infoType_26_do_biaowai = infoType_26_do[infoType_26_do['欠息类型'] == '2']
print('欠息总业务数：',infoType_26_do['欠息余额'].count())
print('欠息总余额数：',infoType_26_do['欠息余额'].sum())
infoType_26_do.drop_duplicates(subset=['贷款卡编码'], keep='last', inplace=True)
print('欠息总客户数：',infoType_26_do['贷款卡编码'].count())

print('欠息(表内)业务数：',infoType_26_do_biaonei['欠息余额'].count())
print('欠息(表内)余额数：',infoType_26_do_biaonei['欠息余额'].sum())
print('欠息(表内)客户数：',infoType_26_do_biaonei['贷款卡编码'].count())

print('欠息(表外)业务数：',infoType_26_do_biaowai['欠息余额'].count())
print('欠息(表外)余额数：',infoType_26_do_biaowai['欠息余额'].sum())
print('欠息(表外)客户数：',infoType_26_do_biaowai['贷款卡编码'].count())
